#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Time          : 2024/4/24 16:35
# File          : mysql_read.py
# @Author       : MingTai
# @Department   : automation_qa

import pandas as pd
from sqlalchemy import create_engine

MYSQL_HOST = "127.0.0.1"
MYSQL_PORT = 3306
MYSQL_USER = "root"
MYSQL_PASSWORD = "123456"
MYSQL_DATABASE = "sylphy"

MYSQL_HOST_2 = "127.0.0.1"
MYSQL_PORT_2 = 3306
MYSQL_USER_2 = "root"
MYSQL_PASSWORD_2 = "123456"
MYSQL_DATABASE_2 = "lixiang_db"


# vehicle_trajectory

def get_create_engine():
    return create_engine(f'mysql+pymysql://{MYSQL_USER}:{MYSQL_PASSWORD}@{MYSQL_HOST}:{MYSQL_PORT}/{MYSQL_DATABASE}')


def get_create_engine_2():
    return create_engine(
        f'mysql+pymysql://{MYSQL_USER_2}:{MYSQL_PASSWORD_2}@{MYSQL_HOST_2}:{MYSQL_PORT_2}/{MYSQL_DATABASE_2}')


def build_join_map_data(vin: str, start_timestamp: float, end_timestamp: float):
    engine = get_create_engine()
    tracks_sql = f"SELECT * FROM table_name WHERE collect_timestamp BETWEEN %s AND %s AND vin = %s AND data_src = 'prod' AND deleted = 0"

    # pandas和sqlalchemy 版本不能太高，会执行SQL报错
    with engine.connect() as conn:
        tracks_df = pd.read_sql_query(sql=tracks_sql, con=conn,
                                      params=[start_timestamp, end_timestamp, vin])

    return tracks_df


def experiment_sql(vin: str, start_timestamp: float, end_timestamp: float):
    engine = get_create_engine()
    scene_df = f"SELECT vin, scene_name, start_timestamp, end_timestamp FROM scene_collection WHERE scene_name IN ('city_noa_available', 'highway_noa_available') AND deleted = 0 AND data_src = 'prod' AND vin = %s AND start_timestamp <= %s AND end_timestamp >= %s"

    # pandas和sqlalchemy 版本不能太高，会执行SQL报错
    with engine.connect() as conn:
        scene_df = pd.read_sql_query(sql=scene_df, con=conn, params=[vin, end_timestamp, start_timestamp])

    print(scene_df)


def insert_db_pandas(vin: str, start_timestamp: float, end_timestamp: float):
    data_insert = build_join_map_data(vin=vin, start_timestamp=start_timestamp, end_timestamp=end_timestamp)
    engine = get_create_engine_2()
    # if_exists='append'把数据添加到现有
    data_insert.to_sql('table_name', con=engine, if_exists='append', index=False)


if __name__ == '__main__':
    vin_main = "LW433B121P1004849"
    start_timestamp = 1713283200
    end_timestamp = 1713369599
    print(experiment_sql(vin=vin_main, start_timestamp=start_timestamp, end_timestamp=end_timestamp))
